<?php
class Shinkensaku_Model_SelStation extends Shinkensaku_Model_Base
{

	/**
	 * 表示用変換
	 *
	 * @param $stationList
	 * @param $params
	 */
	public function convertData($stationList, $params)
	{
    	$DispList = array();
    	$indexNo = 0;
    	$intStExistCnt = 0;
        $beforeEnsenCd = null;

        foreach ($stationList as $stationData)
        {
            $ensenCd = $stationData['ENSEN_CD'];

            // 沿線が異なる場合
            if ($beforeEnsenCd != $ensenCd)
            {
            	$DispList["$ensenCd"]['ENSEN_CD'] = $ensenCd;
            	$DispList["$ensenCd"]['ENSEN_NAME'] = $stationData['ENSEN_NAME'];
                $DispList["$ensenCd"]['stationList'] = array();
            }

            // チェック済みにするか判定
            if (!empty($params['ReStCd']) && in_array($stationData['EKI_CD'], $params['ReStCd']))
            {
            	$stationData['checked'] = "checked='checked'";
            }

            $DispList["$ensenCd"]['stationList'][] = $stationData;

            $beforeEnsenCd = $ensenCd;
        }

        return $DispList;
	}

    /**
     * sql文生成(駅集計テーブル更新処理)
     * @param $params
     * @param $db_params
     * @return sql文
     */
	public function ekiKoushin($params)
	{
        $db_params = array();

    	$strWhere1 ='';
    	$strWhere2 ='';
    	$strWhere3 ='';
    	$strWhere4 = '';
    	$strWhere5 = '';
    	$strWhere6 = '';
    	$strWhere7 = '';

        $RailCds = "'".join("','", array_unique($params['RailCd']))."'";
    	$strWhere1 = " ) AND (M03_ENSEN_CD IN ({$RailCds}) ";
    	$strWhere5 = " ) AND (T54_ENSEN_CD IN ({$RailCds}) ";

    	$PlsFlg = 1;
    	for($i = 0; $i < count($params['PrefCd']) + 1; $i++)
    	{
    		if ($PlsFlg == 0)
    		{
    			$i = $i - 1;
    		}

    		if (str_replace(' ', '', substr("0".$params['PrefCd'][$i], -2)) != '')
    		{
    			if ($i == 0)
    			{
    				$strWhere2 = "(( M03_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere6 = "(( T54_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    			}
    			else
    			{
    				$strWhere2 .= "OR M03_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere6 .= "OR T54_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    			}
    			$PlsFlg = 1;
    		}
    		else
    		{
    			$PlsFlg = 0;
    		}
    	}
    	$strWhere2 .= ") ";
    	$strWhere6 .= ") ";
    	$strWhere5 .= "AND T54_DATA_KUBUN = 0 ";
    	$strWhere6 .= "AND T54_DATA_KUBUN = 0 ";

    	// クエリ作成（集計TBL挿入用のﾃﾞｰﾀ抽出）
$sql = <<< docEnd

			SELECT DISTINCT
				M03_TODOUFUKEN_CD,
				M03_ENSEN_CD
			FROM
				( SELECT
				T54_TODOUFUKEN_CD,
				T54_ENSEN_CD
				FROM T54EKI_SUM
				WHERE
					{$strWhere6}
					{$strWhere5})
				),

				( SELECT DISTINCT
					M03_TODOUFUKEN_CD,
					M03_ENSEN_CD
				FROM M03EKI
				WHERE
					{$strWhere2}
					{$strWhere1})
				)

			WHERE M03_TODOUFUKEN_CD = T54_TODOUFUKEN_CD(+)
			AND M03_ENSEN_CD = T54_ENSEN_CD(+)
			AND T54_ENSEN_CD IS NULL

docEnd;

		$result_sel_for_ins = $this->dbGetList($sql, $db_params);

        // データが存在する場合
        if (count($result_sel_for_ins) > 0)
        {
        	$strWhere9 = '';
        	$strWhere11_1 = '';
        	$strWhere11_2 = '';
        	$strWhere11_3 = '';

        	for ($i = 0; $i < count($result_sel_for_ins); $i++)
        	{
				if ($i == 0)
				{
					$strWhere9    = "( M03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND M03_ENSEN_CD = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_1 = "( T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD01 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_2 = "( T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD02 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_3 = "( T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD03 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
				}
				else
				{
					$strWhere9    .= ") OR ( M03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND M03_ENSEN_CD = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_1 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD01 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_2 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD02 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
					$strWhere11_3 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M03_TODOUFUKEN_CD']}' AND T03_ENSEN_CD03 = '{$result_sel_for_ins[$i]['M03_ENSEN_CD']}' ";
				}
        	}

        	// クエリ作成（データ挿入処理）
$sql = <<< docEnd
				INSERT INTO T54EKI_SUM

					( SELECT
						M03_TODOUFUKEN_CD,
						M03_ENSEN_CD,
						M03_EKI_CD,
						M03_EKI_NAME,
						SUM(EKI_HEYA_CNT) AS EKI_HEYA_CNT,
						SYSDATE,
						0
					FROM

						( SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							TGT_RENRAKU_CNT AS EKI_HEYA_CNT
						FROM M03EKI,

							( SELECT
								T63_RENRAKU_CD AS TGT_RENRAKU_CD,
								SUM(T63_COUNT) AS TGT_RENRAKU_CNT
							FROM T63RENRAKU_EKI_SUM
							WHERE T63_DATA_KUBUN = 0
							GROUP BY T63_RENRAKU_CD )

						WHERE ( M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD
						AND M03_RENRAKU_EKI_CD IS NOT NULL
						AND (  {$strWhere9} )))

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD01
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD01 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_1} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14,'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD02
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD02 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_2} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14,'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD03
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD03 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_3} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14,'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME

						)
					GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME
					)

docEnd;

			$db = $this->getDbConnection();
	        $this->startTransaction($db);
	        try {
	            $this->dbExecQuery($sql, $db_params, $db);
	            $this->commitTransaction($db);
	        } catch (Exception $e) {
	            $this->rollbackTransaction($db);
	        }
	        $db->closeConnection();
        }

    	// クエリ作成（削除対象用ﾃﾞｰﾀ抽出）
$sql = <<< docEnd

			SELECT DISTINCT
				T54_TODOUFUKEN_CD,
				T54_ENSEN_CD
			FROM T54EKI_SUM,

			 ( SELECT DISTINCT
				M03_TODOUFUKEN_CD,
				M03_ENSEN_CD
			FROM M03EKI
			WHERE
				{$strWhere2}
				{$strWhere1})

			GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD
			)

			WHERE T54_KOUSHIN < SYSDATE - 30 / 1440
			AND T54_DATA_KUBUN = 0
			AND M03_TODOUFUKEN_CD = T54_TODOUFUKEN_CD
			AND M03_ENSEN_CD = T54_ENSEN_CD

docEnd;

        $result_sel_for_del = $this->dbGetList($sql, $db_params);

        // データが存在する場合
        if (count($result_sel_for_del) > 0)
        {
        	$strWhere9 = '';
        	$strWhere11_1 = '';
        	$strWhere11_2 = '';
        	$strWhere11_3 = '';
        	$strWhere10 = '';

        	for ($i = 0; $i < count($result_sel_for_del); $i++)
        	{
				if ($i == 0)
				{
					$strWhere9 	  = "( M03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND M03_ENSEN_CD = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_1 = "( T03_ENSEN_CD01 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_2 = "( T03_ENSEN_CD02 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_3 = "( T03_ENSEN_CD03 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere10   = "( T54_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND T54_ENSEN_CD = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
				}
				else
				{
					$strWhere9	  .= ") OR (M03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND M03_ENSEN_CD = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_1 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND T03_ENSEN_CD01 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_2 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND T03_ENSEN_CD02 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere11_3 .= ") OR (T03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND T03_ENSEN_CD03 = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
					$strWhere10	  .= ") OR (T54_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T54_TODOUFUKEN_CD']}' AND T54_ENSEN_CD = '{$result_sel_for_del[$i]['T54_ENSEN_CD']}' ";
				}
        	}

        	$strWhere10 .= "AND T54_DATA_KUBUN = 0 ";

    	// クエリ作成（データ削除処理）
$sql = <<< docEnd

	DELETE FROM T54EKI_SUM
	WHERE {$strWhere10})

docEnd;

		$db = $this->getDbConnection();
        $this->startTransaction($db);
        try {
            $this->dbExecQuery($sql, $db_params, $db);
            $this->commitTransaction($db);
        } catch (Exception $e) {
            $this->rollbackTransaction($db);
        }
        $db->closeConnection();

        // クエリ作成（集計TBLにﾃﾞｰﾀを追加）
$sql = <<< docEnd

				INSERT INTO T54EKI_SUM
				(
					SELECT
						M03_TODOUFUKEN_CD,
						M03_ENSEN_CD,
						M03_EKI_CD,
						M03_EKI_NAME,
						SUM(EKI_HEYA_CNT) AS EKI_HEYA_CNT,
						SYSDATE,
						0
					FROM

						( SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							TGT_RENRAKU_CNT AS EKI_HEYA_CNT
						FROM M03EKI,

							( SELECT
								T63_RENRAKU_CD AS TGT_RENRAKU_CD,
								SUM(T63_COUNT) AS TGT_RENRAKU_CNT
							FROM T63RENRAKU_EKI_SUM
							WHERE T63_DATA_KUBUN = 0
							GROUP BY T63_RENRAKU_CD )
							WHERE ( M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD
							AND M03_RENRAKU_EKI_CD IS NOT NULL
							AND ( {$strWhere9} )))

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD01
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD01 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_1} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14, 'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD02
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD02 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_2} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14, 'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME

						UNION ALL

						SELECT
							M03_TODOUFUKEN_CD,
							M03_ENSEN_CD,
							M03_EKI_CD,
							M03_EKI_NAME,
							COUNT( T03_HEYA_CD ) AS EKI_HEYA_CNT
						FROM M03EKI, T03WEB_HEYA
						WHERE ( M03_EKI_CD = T03_EKI_CD03
						AND M03_RENRAKU_EKI_CD IS NULL
						AND T03_RENRAKU_EKI_CD03 IS NULL
						AND ( {$strWhere9} ))
						AND ( {$strWhere11_3} ))
						AND T03_TOUROKUBI_STR >= TO_CHAR( SYSDATE-14, 'YYYYMMDD' ))

						GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME
						)
					GROUP BY M03_TODOUFUKEN_CD, M03_ENSEN_CD, M03_EKI_CD, M03_EKI_NAME
					)

docEnd;

	        $db = $this->getDbConnection();
	        $this->startTransaction($db);
	        try {
	            $this->dbExecQuery($sql, $db_params, $db);
	            $this->commitTransaction($db);
	        } catch (Exception $e) {
	            $this->rollbackTransaction($db);
	        }
	        $db->closeConnection();
        }
	}

    /**
     * sql文生成(OemCdなし)
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateSql($params, &$db_params)
    {
    	$strWhere1 ='';
    	$strWhere2 ='';
    	$strWhere3 ='';
    	$strWhere4 = '';
    	$strWhere5 = '';
    	$strWhere6 = '';
    	$strWhere7 = '';

        $RailCds = "'".join("','", array_unique($params['RailCd']))."'";
    	$strWhere1 = " ) AND T54_ENSEN_CD IN ({$RailCds}) ";
    	$strWhere4 = " ) AND M03_ENSEN_CD IN ({$RailCds}) ";
    	$strWhere6 = "  AND T57_ENSEN_SHIKUCHOUSON_CD IN ({$RailCds}) ";

    	$PlsFlg = 1;
    	for($i = 0; $i < count($params['PrefCd']); $i++)
    	{
    		if ($PlsFlg == 0)
    		{
    			$i = $i - 1;
    		}

    		if (str_replace(' ', '', substr("0".$params['PrefCd'][$i], -2)) != '')
    		{
    			if ($i == 0)
    			{
    				$strWhere2 = "(( T54_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere3 = "(M43_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere5 = "(( M03_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere7 = "(( T57_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    			}
    			else
    			{
    				$strWhere2 .= "OR T54_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere3 .= "OR M43_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere5 .= "OR M03_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    				$strWhere7 .= "OR T57_TODOUFUKEN_CD = '" . substr("0".$params['PrefCd'][$i], -2) . "'";
    			}
    			$PlsFlg = 1;
    		}
    		else
    		{
    			$PlsFlg = 0;
    		}
    	}
    	$strWhere2 .= " )";
    	$strWhere3 .= " )";
    	$strWhere5 .= " )";
    	$strWhere7 .= " )";
    	$strWhere2 .= "AND T54_DATA_KUBUN = 0 ";
    	$strWhere7 .= "AND T57_DATA_KUBUN = 0 ";

    	// クエリ作成
$sql = <<< docEnd

		SELECT
			T2.ENSEN_HEYA_CNT AS ENSEN_HEYA_CNT,
			T1.EKI_HEYA_CNT AS REC_CNT,
			T1.ENSEN_CD AS ENSEN_CD,
			T1.EKI_CD AS EKI_CD,
			T1.ENSEN_NAME AS ENSEN_NAME,
			T1.ENSEN_KANA AS ENSEN_KANA,
			T1.EKI_CD AS SORT,
			CASE WHEN T1.EKI_NAME = '＊＊＊' THEN T3.TODOUFUKEN || T1.ENSEN_NAME ELSE T1.EKI_NAME END AS EKI_NAME
		FROM

			( SELECT
				NVL(T54.EKI_HEYA_CNT, 0) AS EKI_HEYA_CNT,
				M03.M03_TODOUFUKEN_CD AS TODOUFUKEN_CD,
				M03.M03_ENSEN_CD AS ENSEN_CD,
				M03.M03_EKI_CD AS EKI_CD,
				M03.M02_ENSEN_NAME AS ENSEN_NAME,
				M03.M03_EKI_NAME AS EKI_NAME,
				M03.M02_ENSEN_KANA AS ENSEN_KANA
			FROM

				( SELECT
					SUM(T54_EKI_CNT) AS EKI_HEYA_CNT,
					T54_EKI_CD
				FROM T54EKI_SUM
				WHERE
					{$strWhere2}
					{$strWhere1}
				GROUP BY T54_EKI_CD ) T54,

				( SELECT
					M03_EKI_CD,
					M03_ENSEN_CD,
					M03_TODOUFUKEN_CD,
					M02_ENSEN_NAME,
					M03_EKI_NAME,
					M02_ENSEN_KANA,
					M03_TODOUFUKEN
				FROM M02ENSEN, M03EKI
				WHERE
					{$strWhere5}
					{$strWhere4}
				AND M02_ENSEN_CD = M03_ENSEN_CD ) M03
			WHERE T54.T54_EKI_CD(+) = M03.M03_EKI_CD ) T1,

			( SELECT
				SUM(T57_ENSEN_SHIKUCHOUSON_CNT) AS ENSEN_HEYA_CNT,
				T57_ENSEN_SHIKUCHOUSON_CD AS ENSEN_CD
			FROM T57ENSEN_SHIKUCHOUSON_SUM
			WHERE
				{$strWhere7})
			AND T57_ENSEN_SHIKUCHOUSON_FLAG = 0
				{$strWhere6}
			GROUP BY T57_ENSEN_SHIKUCHOUSON_CD ) T2,

			( SELECT
				M43_TODOUFUKEN_CD AS TODOUFUKEN_CD,
				M43_TODOUFUKEN AS TODOUFUKEN
			FROM M43TODOUFUKEN
			WHERE
				{$strWhere3}) T3

		WHERE T1.ENSEN_CD = T2.ENSEN_CD
		AND T1.TODOUFUKEN_CD = T3.TODOUFUKEN_CD
		AND EKI_HEYA_CNT <> 0
		ORDER BY ENSEN_KANA ASC, SORT ASC

docEnd;

		return $sql;
    }

    /**
     * sql文生成(OemCdあり)
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateOemSql($params, &$db_params)
    {
        $strWhere3_1 = '';
        $strWhere3_2 = '';
        $strWhere3_3 = '';
        $strWhere4 = '';
        $RailCds = '';

    	// OemCdに基づく条件文を生成
        $strWhereOemM05 = $this->generateCondOem($params);

        // 都道府県コードに基づく条件文作成
        $this->setCondPrefCd($params);

        $RailCds = "'".join("','", array_unique($params['RailCd']))."'";
        // 沿線コードに基づく条件文を生成
        $strWhere3_1 = " T03_ENSEN_CD01 IN ({$RailCds}) ";
        $strWhere3_2 = " T03_ENSEN_CD02 IN ({$RailCds}) ";
        $strWhere3_3 = " T03_ENSEN_CD03 IN ({$RailCds}) ";
        $strWhere4 = " M03_ENSEN_CD IN ({$RailCds}) ";

    	// クエリ作成

        $sql .= " SELECT";
		$sql .= "         T.ENSEN_REC_COUNT AS ENSEN_HEYA_CNT";
		$sql .= "         ,NVL(T.EKI_REC_COUNT, 0) AS REC_CNT";
		$sql .= "         ,T.M03_ENSEN_CD AS ENSEN_CD";
		$sql .= "         ,T.M03_EKI_CD AS EKI_CD";
		$sql .= "         ,A.M02_ENSEN_NAME AS ENSEN_NAME";
		$sql .= "         ,A.M02_ENSEN_KANA AS ENSEN_KANA";
		$sql .= "         ,T.M03_EKI_CD AS SORT";
		$sql .= "         ,CASE";
		$sql .= "             WHEN T.M03_EKI_NAME = '＊＊＊' THEN B.M43_TODOUFUKEN || A.M02_ENSEN_NAME";
		$sql .= "             ELSE T.M03_EKI_NAME";
		$sql .= "         END AS EKI_NAME";
		$sql .= "     FROM";
		$sql .= "         M02ENSEN A";
		$sql .= "         ,M43TODOUFUKEN B";
		$sql .= "         ,(";
		$sql .= "             SELECT";
		$sql .= "                     A2.M03_ENSEN_CD";
		$sql .= "                     ,A2.M03_TODOUFUKEN_CD";
		$sql .= "                     ,A2.M03_EKI_CD";
		$sql .= "                     ,A2.M03_EKI_NAME";
		$sql .= "                     ,NVL(SUM(A1.REC_CNT), 0) AS EKI_REC_COUNT";
		$sql .= "                     ,NVL(A3.REC_CNT, 0) AS ENSEN_REC_COUNT";
		$sql .= "                 FROM";
		$sql .= "                     (";
		$sql .= "                         SELECT";
		$sql .= "                                 M03_EKI_CD AS T03_EKI_CD";
		$sql .= "                                 ,REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                 (";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                             ,SUM(T63_COUNT) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T63RENRAKU_EKI_SUM";
		$sql .= "                                         WHERE";
		$sql .= "                                             (".$this ->strWhere2.")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NOT NULL";
		$sql .= "                                             AND T63_TENPO_CD IS NOT NULL";
		$sql .= "                                             AND T63_RENRAKU_CD = M03_RENRAKU_EKI_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T63_DATA_KUBUN = 1";
		}else{
			$sql .= "                                         AND T63_DATA_KUBUN = 0";
		}
		$sql .= "                                             AND M05_TENPO_CD = T63_TENPO_CD";
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (".$this -> strWhere2.")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_1 .")";
		$sql .= "                                             AND T03_EKI_CD01 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD01 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_2 .")";
		$sql .= "                                             AND T03_EKI_CD02 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD02 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_3 .")";
		$sql .= "                                             AND T03_EKI_CD03 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD03 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_EKI_CD";
		$sql .= "                                 )";
		$sql .= "                             GROUP BY";
		$sql .= "                                 M03_EKI_CD";
		$sql .= "                                 ,REC_CNT";
		$sql .= "                     ) A1";
		$sql .= "                     ,(";
		$sql .= "                         SELECT";
		$sql .= "                                 M03_ENSEN_CD";
		$sql .= "                                 ,M03_TODOUFUKEN_CD";
		$sql .= "                                 ,M03_EKI_CD";
		$sql .= "                                 ,M03_EKI_NAME";
		$sql .= "                             FROM";
		$sql .= "                                 M03EKI";
		$sql .= "                             WHERE";
		$sql .= "                                 (". $this -> strWhere2 .")";
		$sql .= "                                 AND (". $strWhere4 .")";
		$sql .= "                     ) A2";
		$sql .= "                     ,(";
		$sql .= "                         SELECT";
		$sql .= "                                 M03_ENSEN_CD AS T03_ENSEN_CD";
		$sql .= "                                 ,SUM(REC_CNT) AS REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                 (";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                             ,SUM(T63_COUNT) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T63RENRAKU_EKI_SUM";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NOT NULL";
		$sql .= "                                             AND T63_TENPO_CD IS NOT NULL";
		$sql .= "                                             AND T63_RENRAKU_CD = M03_RENRAKU_EKI_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T63_DATA_KUBUN = 1";
		}else{
			$sql .= "                                         AND T63_DATA_KUBUN = 0";
		}
		$sql .= "                                             AND M05_TENPO_CD = T63_TENPO_CD";
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_1 .")";
		$sql .= "                                             AND T03_EKI_CD01 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD01 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_2 .")";
		$sql .= "                                             AND T03_EKI_CD02 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD02 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                     UNION ALL";
		$sql .= "                                     SELECT";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                             ,COUNT(T03_HEYA_CD) AS REC_CNT";
		$sql .= "                                         FROM";
		$sql .= "                                             M03EKI";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                             ,T03WEB_HEYA";
		$sql .= "                                         WHERE";
		$sql .= "                                             (". $this -> strWhere2 .")";
		$sql .= "                                             AND (". $strWhere4 .")";
		$sql .= "                                             AND (". $this -> strWhere1 .")";
		$sql .= "                                             AND (". $strWhere3_3 .")";
		$sql .= "                                             AND T03_EKI_CD03 = M03_EKI_CD";
		$sql .= "                                             AND T03_RENRAKU_EKI_CD03 IS NULL";
		$sql .= "                                             AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .=                                               $strWhereOemM05;
		$sql .= "                                         GROUP BY";
		$sql .= "                                             M03_ENSEN_CD";
		$sql .= "                                 )";
		$sql .= "                             GROUP BY";
		$sql .= "                                 M03_ENSEN_CD";
		$sql .= "                     ) A3";
		$sql .= "                 WHERE";
		$sql .= "                     A1.T03_EKI_CD(+) = A2.M03_EKI_CD";
		$sql .= "                     AND A2.M03_ENSEN_CD = A3.T03_ENSEN_CD";
		$sql .= "                 GROUP BY";
		$sql .= "                     A2.M03_ENSEN_CD";
		$sql .= "                     ,A2.M03_TODOUFUKEN_CD";
		$sql .= "                     ,A2.M03_EKI_CD";
		$sql .= "                     ,A2.M03_EKI_NAME";
		$sql .= "                     ,A3.REC_CNT";
		$sql .= "         ) T";
		$sql .= "     WHERE";
		$sql .= "         T.M03_ENSEN_CD = A.M02_ENSEN_CD";
		$sql .= "         AND T.M03_TODOUFUKEN_CD = B.M43_TODOUFUKEN_CD";
		$sql .= "         AND T.EKI_REC_COUNT <> 0";
		$sql .= "     ORDER BY";
		$sql .= "         A.M02_ENSEN_KANA ASC";
		$sql .= "         ,T.M03_EKI_CD ASC";

		return $sql;
    }
}
?>
